package by.bsu.touragency.db.dao;

import java.sql.PreparedStatement;
import java.sql.SQLException;
import org.apache.log4j.Logger;
import by.bsu.touragency.db.beans.InformationClient;
import by.bsu.touragency.db.connection.DBConnector;

public class InformationClientDAO extends AbstractDAO {

	public static final String SQL_INFORMATIONCLIENT_CREATE = " INSERT INTO tourAgencyDB.informationClient (FIO, passport, telephone,clientId) VALUES (? , ?, ?, ?);";
	public static final String SQL_INFORMATIONCLIENT_CREATE_GET_ID = "SELECT * FROM tourAgencyDB.informationClient WHERE passport = ?  ;";
	public static final String SQL_INFORMATIONCLIENT_READ_BY_ID = "SELECT * FROM tourAgencyDB.informationClient WHERE (clientId= ?);";
	public static final String SQL_INFORMATIONCLIENT_UPDATE = "UPDATE tourAgencyDB.informationClient  SET FIO = ? , passport = ? ,telephone = ? WHERE (clientId = ?);";
	public static final String SQL_INFORMATIONCLIENT_DELETE = "DELETE FROM tourAgencyDB.informationClient WHERE id = ?";
	public static final String SQL_INFORMATIONCLIENT_IS_EXIST = " SELECT COUNT(*) AS rowcount FROM tourAgencyDB.informationClient WHERE ( id  = ? );";

	private static Logger logger = Logger.getLogger("log");

	public boolean isExistById(int id) {
		logger.info("Is information about client exist");
		try {// 1
			connector = new DBConnector();
			PreparedStatement statement = connector
					.getPreparedStatement(SQL_INFORMATIONCLIENT_IS_EXIST);
			int n;
			try {// 3
				statement.setInt(1, id);
				resultSet = statement.executeQuery();
				n = -2;
				resultSet.next();
				n = resultSet.getInt("rowcount");
			} finally {
			}// 3
			if (resultSet != null) {
				resultSet.close();
			}
			if (statement != null) {
				statement.close();
			}
			if (n == 0) {
				logger.debug("There is no client info");
				return false;
			} else {
				logger.debug("Client info already exist");
				return true;
			}
		} catch (SQLException e) {
			logger.error("SQL exeption");
			return false;
		} catch (Exception e) {
			logger.error("Undefined exeption");
			return false;
		} finally {
			logger.debug("Close connection");
			close();
		}
	}

	// C
	public InformationClient create(InformationClient inf) {
		logger.info("Creat info about this client");
		try {
			System.out.println("create");
			connector = new DBConnector();
			PreparedStatement statement = connector
					.getPreparedStatement(SQL_INFORMATIONCLIENT_CREATE);
			statement.setString(1, inf.getFIO());
			statement.setString(2, inf.getPassport());
			statement.setString(3, inf.getTel());
			statement.setInt(4, inf.getClientId());
			statement.executeUpdate();

			statement = connector
					.getPreparedStatement(SQL_INFORMATIONCLIENT_CREATE_GET_ID);
			statement.setString(1, inf.getPassport());
			resultSet = statement.executeQuery();

			if (resultSet.next()) {
				inf.setId(resultSet.getInt("id"));
			}
			if (statement != null) {
				statement.close();
			}
		} catch (SQLException e) {
			logger.error("SQL exeption");
		} catch (Exception e) {
			logger.error("Undefined exeption");
		} finally {
			logger.debug("Close connection");
			close();
		}
		return inf;
	}

	// R
	public InformationClient readByClientID(int clientId) {
		logger.info("Read info about client by id");
		InformationClient inf = new InformationClient();
		try {
			connector = new DBConnector();
			PreparedStatement statement = connector
					.getPreparedStatement(SQL_INFORMATIONCLIENT_READ_BY_ID);
			System.out.println(clientId);
			statement.setInt(1, clientId);
			resultSet = statement.executeQuery();
			while (resultSet.next()) {
				inf.setId(resultSet.getInt("id"));
				inf.setFIO(resultSet.getString("FIO"));
				inf.setPassport(resultSet.getString("passport"));
				inf.setTel(resultSet.getString("telephone"));
				inf.setClientId(clientId);
			}
			if (statement != null) {
				statement.close();
			}
		} catch (SQLException e) {
			logger.error("SQL exeption");
		} catch (Exception e) {
			logger.error("Undefined exeption");
		} finally {
			logger.debug("Close connection");
			close();
		}
		return inf;
	}

	// U
	public void update(InformationClient inf) {
		logger.info("Update client info");
		if (!isExistById(inf.getId())) {
			try {
				connector = new DBConnector();
				PreparedStatement statement = connector
						.getPreparedStatement(SQL_INFORMATIONCLIENT_UPDATE);
				statement.setString(1, inf.getFIO());
				statement.setString(2, inf.getPassport());
				statement.setString(3, inf.getTel());
				statement.setInt(4, inf.getClientId());
				statement.executeUpdate();

				if (statement != null) {
					statement.close();
				}
			} catch (SQLException e) {
				logger.error("SQL exeption");
			} catch (Exception e) {
				logger.error("Undefined exeption");
			} finally {
				logger.debug("Close connection");
				close();
			}
		} else
			create(inf);
	}

	// D
	public void deleteById(int id) {
		logger.info("Delete client info by id");
		try {
			connector = new DBConnector();
			PreparedStatement statement = connector
					.getPreparedStatement(SQL_INFORMATIONCLIENT_DELETE);
			statement.setInt(1, id);
			statement.executeUpdate();
			if (statement != null) {
				statement.close();
			}
		} catch (SQLException e) {
			logger.error("SQL exeption");
		} catch (Exception e) {
			logger.error("Undefined exeption");
		} finally {
			logger.debug("Close connection");
			close();
		}
	}
}
